import requests
import pymongo
import pymysql
from lxml import etree

datas = {}
for page in range(1, 11):
    url = f'http://quotes.toscrape.com/page/{page}/'
    res = requests.get(url)
    tree = etree.HTML(res.text)
    items = tree.xpath('//div[@class="quote"]')
    text_datas = []
    for item in items:
        content = item.xpath('.//span[@class="text"]/text()')[0][1:-1]
        author = item.xpath('.//small[@class="author"]/text()')[0]
        tags = item.xpath('.//a[@class="tag"]/text()')
        text_datas.append({
            'content': content,
            'author': author,
            'tags': tags,
        })
    datas[f'page{page}'] = text_datas

# 存mongodb
# client = pymongo.MongoClient('localhost', 27017)
# db = client['toscrape']
# collection = db['toscrape']
# collection.insert_one(datas)

# 存mysql
conn = pymysql.connect(host='localhost', user='root', database='toscrape', passwd='123456', charset='utf8')
cur = conn.cursor()
# 删除之前的tag表 有外键先删除tags
cur.execute('drop table if exists tags')
# 删除之前有的文章表
cur.execute('drop table if exists content')
# 创建文章表
cur.execute(
    "create table if not exists content (id int not null primary key auto_increment,content text not null,author varchar(50) not null)")
# 创建tag表
cur.execute(
    "create table if not exists tags(id int not null primary key auto_increment,tag char(50) not null,content_id int,constraint fk_cotent_tag foreign key (content_id) references content(id) on delete cascade on update cascade) ")

for key in datas:
    for items in datas[key]:
        content = items['content']
        author = items['author']
        tags = items['tags']
        cur.execute('insert into content values(%s, %s, %s)', (0, content, author))
        content_id = cur.lastrowid
        for tag in tags:
            cur.execute('insert into tags values(%s,%s,%s)', (0, tag, content_id))


conn.commit()
conn.close()
